import math

import snowflake.client
from flask import Blueprint,request,jsonify
import json
from util.myredis import r

from models.user import *

classidy_blue = Blueprint("classidyblue",__name__)


@classidy_blue.route('/getone')
def getone():
    clasify =  db.session.execute("select * from classify")
    clist = list(clasify)
    list1 = []
    menulist = []
    for i in clist:
        if i[4] == 0:
            if i[4] not in menulist:
                list1.append({'value':i[0],'label':i[3],'children':[]})
                menulist.append([i[0]])
    for j in clist:
        for index,p in enumerate(list1):
            if j[4] == p['value']:
                list1[index]['children'].append({'value':j[0],'label':j[3],'pid':j[4],'children':[]})
    for x in clist:
        for index,p in enumerate(list1):
            aa = list1[index]['children']
            for index,t in enumerate(aa):
                if x[4] == t['value']:
                    aa[index]['children'].append({'value':x[4],'label':x[3],'id':x[0]})
    print(list1)
    return jsonify({'code':200,'data':list1})

# 个人主页添加修改
@classidy_blue.route('/setbasic',methods=['POST'])
def setbasic():
    data = request.data
    if data:
        data = json.loads(data)
        userid = data['userid']
        name = data['name']
        sex = data['sex']
        identity = data['identity']
        addr = data['addr']
        email = data['email']
        bas = basic.query.filter_by(userid=userid).first()
        if bas:
            bas.sex = sex
            bas.name = name
            bas.email = email
            bas.identity = identity
            bas.addr = addr
            db.session.add(bas)
            db.session.commit()
            return jsonify({'code':200,'msg':'修改成功'})
        bsc = basic(name=name,sex=sex,identity=identity,addr=addr,email=email,userid=userid)
        db.session.add(bsc)
        db.session.commit()
        return jsonify({'code':200,'msg':'ok'})
    else:
        return jsonify({'code':300,'msg':'输入信息'})
# 查找当先用户信息是否存在
@classidy_blue.route('/getbasic')
def getbasic():
    userid = request.args.get('userid')
    bas = basic.query.filter_by(userid=userid).first()
    print(bas.id)
    list=[]
    list.append({'id':bas.id,'name':bas.name,'sex':bas.sex,'identity':bas.identity,'addr':bas.addr,'email':bas.email})

    print(list)
    return jsonify({'code':200,'list':list})
# 个人主页服务添加修改
@classidy_blue.route('/setserve',methods=['POST'])
def setserve():
    data = request.data
    if data:
        data = json.loads(data)
        userid = data['userid']
        ability = data['ability']
        skil = data['skil']
        pay = data['pay']
        close = data['close']
        work = data['work']
        worktime = data['worktime']
        bas = serve.query.filter_by(userid=userid).first()
        if bas:
            bas.ability = ability
            bas.skil = skil
            bas.close = close
            bas.pay = pay
            bas.work = work
            bas.worktime = worktime
            db.session.add(bas)
            db.session.commit()
            return jsonify({'code':200,'msg':'修改成功'})
        bsc = serve(ability=ability,skil=skil,pay=pay,close=close,work=work,worktime=worktime)
        db.session.add(bsc)
        db.session.commit()
        return jsonify({'code':200,'msg':'ok'})
    else:
        return jsonify({'code':300,'msg':'输入信息'})
# 查找当先用户信息是否存在
@classidy_blue.route('/getserve')
def getserve():
    userid = request.args.get('userid')
    bas = serve.query.filter_by(userid=userid).first()


    list=[]
    list.append({'id':bas.id,'ability':bas.ability,'skil':bas.skil,'close':bas.close,'pay':bas.pay,'work':bas.work,'worktime':bas.worktime})
    print(list)
    return jsonify({'code':200,'list':list})

# @classidy_blue.route('/gettypes')
# def gettypes():
#     type1 = types.query.all()
#     list = []
#     mlist = []
#     for i in type1:
#         if i.pid == 0:
#             if i.id not in mlist:
#                 list.append({'id':i.id,'name':i.name,'son':[]})
#                 mlist.append([i.id])
#
#     for j in type1:
#         for index,p in enumerate(list):
#             if j.pid == p['id']:
#                 list[index]['son'].append({'id':j.id,'oname':j.name,'pid':j.pid,'sons':[]})
#     for x in type1:
#         for index,p in enumerate(list):
#             aa =list[index]['son']
#             for indexs,t in enumerate(aa):
#                 if x.pid == t['id']:
#                     aa[indexs]['sons'].append({'id':x.id,'tname': x.name})
#
#     print(list)
#
#     return jsonify({'code':200,'list':list})


# 无限递归
@classidy_blue.route('/gettypes')
def gettypes():
    type1 = types.query.all()
    list1=[]
    rlist = []
    dict = {}
    for i in type1:
        list1.append({'id':i.id,'name':i.name,'pid':i.pid})
    for i in list1:
        k = i['id']
        dict[k] =i

    for j in list1:
        pid = j['pid']
        if pid == 0:
            rlist.append(j)
        else:
            if 'son' not in dict[pid]:
                dict[pid]['son']=[]
            dict[pid]['son'].append(j)
    print('rlist',rlist)
    return jsonify({'code':200,'list':rlist})

# 雪花算法
def create_order(tablename,count):
    # 生成唯一id
    code = snowflake.client.get_guid()
    # 用redis解决时钟回拨，根据key查询上一次存入的结果，和当前生成的对比，如果当前小于redis中存储的，说明发生时钟回拨
    value = r.str_get(tablename)
    if value:
        if int(code)<int(value):
            create_order(tablename,count)
    r.str_add(tablename,code)
    r.list_push('projectkey',code)
    number = hash(code)%int(count)

    if number == 0:
        return tablename,code
    else:
        return tablename+str(number),code


def ontim_time(tablename, count):
    code = snowflake.client.get_guid()
    value = r.str_get(tablename)
    if value:
        if int(code) < int(value):
            create_order(tablename, count)
    r.str_add(tablename, code)
    r.list_push('ontimekey',code)

    number = hash(code) % int(count)
    if number == 0:
        return tablename, code
    else:
        return tablename + str(number), code


@classidy_blue.route('/getproject',methods=['POST'])
def getproject():
    data = json.loads(request.data)
    name = data['name']
    title = data['title']
    descs = data['descs']
    type = data['type']
    mobile = data['mobile']
    trade = data['trade']
    hire = data['hire']
    daily = data['daily']
    money = float(data['money'])
    typeid = data['typeid']
    print(typeid)
    userid= data['userid']
    if name == '按项目付费':
        user = User.query.filter_by(id=userid).first()
        print('*****',user.money)
        if money >= user.money:
            return jsonify({'code':'100020','msg':'余额不足'})
        tmoney = user.money-money
        lockmoney = user.lockmoney+money
        User.query.filter_by(id=userid).update({'money':tmoney,'lockmoney':lockmoney})
        db.session.commit()
        tablename,code = create_order('project',3)
        # 转换成表名
        tablename = globals().get(f'{tablename}')
        pro = tablename(name=name,title=title,descs=descs,type=type,money=money,mobile=mobile,trade=trade,code=code,userid=userid,typeid=typeid)
        db.session.add(pro)
        db.session.commit()
        return jsonify({'code': 200, 'msg': '发布成功'})
    if name == '按时收费':

        user = User.query.filter_by(id=userid).first()
        if money >= user.money:
            return jsonify({'code': '100020', 'msg': '余额不足'})
        tmoney = user.money - money
        lockmoney = user.lockmoney + money
        User.query.filter_by(id=userid).update({'money': tmoney, 'lockmoney': lockmoney})
        db.session.commit()
        tablename, code = create_order('ontime', 3)
        print(tablename, code)
        # 转换成表名
        tablename = globals().get(f'{tablename}')
        ontim = tablename(name=name,title=title,descs=descs,type=type,hire=hire,mobile=mobile,trade=trade,daily=daily,money=money,code=code,userid=userid)
        db.session.add(ontim)
        db.session.commit()
        return jsonify({'code':200,'msg':'发布成功'})
# 产看发布信息信息  分页展示
@classidy_blue.route('/getprojectall')
def getprojectall():
    page = request.args.get('page')
    page_size = request.args.get('page_size')


    start = (int(page) - 1) * int(page_size)

    q1 = db.session.query(project).filter(project.status == 1)
    q2 = db.session.query(project1).filter(project1.status == 1)
    q3 = db.session.query(project2).filter(project2.status == 1)
    q4 = db.session.query(ontime).filter(ontime.status == 0)
    q5 = db.session.query(ontime1).filter(ontime1.status == 0)
    q6 = db.session.query(ontime2).filter(ontime2.status == 0)
    ontimes = q4.union(q5).union(q6).offset(start).limit(page_size).all()
    dict1= [] #按时计费
    for i in ontimes:
        dict1.append({
            'code': str(i.code),
            'name': i.name,
            'title': i.title,
            'descs': i.descs,
            'type': i.type,
            'mobile': i.mobile,
            'trade': i.trade,
            'money': i.money,
            'status': i.status,
            'userid': i.userid,
            'hire': i.hire,
            'daily': i.daily,
            'create_time': str(i.create_time),
            'update_time': str(i.update_time)
        })


    ret = q1.union(q2).union(q3).offset(start).limit(page_size).all() # 数据重复，只留一条


    ret1 = q1.union(q2).union(q3).count()

    list = [] #按项目计费
    for i in ret:
        list.append({
            'code': str(i.code),
            'name': i.name,
            'title': i.title,
            'descs': i.descs,
            'type': i.type,
            'money': i.money,
            'mobile': i.mobile,
            'trade': i.trade,
            'status': i.status,
            'userid': i.userid,
            'create_time':str(i.create_time),
            'update_time':str(i.update_time)
        })

    alllist = [] #全部数据
    for i in list:
        alllist.append(i)
    for j in dict1:
        alllist.append(j)

    return jsonify({'code':200,'prolist':list,'tcount':ret1,'all':alllist,'onlist':dict1})


@classidy_blue.route('/label_index')
def label_index():
    input = request.args.get('title')
    lst = []
    res = project.query.filter(project.title.ilike(f"%{input}%")).all()
    if res:
        lst.append(res)
    ress = project1.query.filter(project1.title.ilike(f"%{input}%")).all()
    if ress:
        lst.append(ress)
    resa = project2.query.filter(project2.title.ilike(f"%{input}%")).all()
    if resa:
        lst.append(resa)
    new_list = [j for i in lst for j in i]
    print(new_list)
    list = []
    if new_list:
        for s in new_list:
            list.append({
                'code': str(s.code),
                'name': s.name,
                'title': s.title,
                'descs': s.descs,
                'type': s.type,
                'money': s.money,
                'mobile': s.mobile,
                'trade': s.trade,
                'status': s.status,
                'userid': s.userid,
                'create_time': str(s.create_time),
                'update_time': str(s.update_time)
            })
        return jsonify({
            'code': 200,
            'msg': "OK",
            'data':list
        })
    else:
        return jsonify({
            'code': 400,
            'msg': "数据不存在"
        })


def getcateid(ids):
    sql = 'select id from types where pid in ('+str(ids)+")"
    res = db.session.execute(sql)
    data = res.fetchall()
    list = []
    if data:
        # ids = ""
        for i in data:
            list.append(i)

        #     ids + ids +str(i['id'])+","
        # idslist = ids.split(',')
        # arrlist = idslist[0:-1]
        # idsstr = ','
        # ids = idsstr.join(arrlist)

        # return getcateid(ids)
    return ids,list

@classidy_blue.route('/getipproject')
def gettypesid1():
    tid = request.args.get('id')
    ids, list = getcateid(tid)
    list1 = []
    for i in list:
        sql = db.session.execute("select * from (select * from project where status=1 union all select * from project1 where status=1 union all select * from project2 where status=1)t where t.typeid=%d"%(int(i.id)))
        print('111',sql)
        if sql:
            for i in sql:
                print('i',i)
                list1.append({
                    'code': str(i.code),
                    'name': i.name,
                    'title': i.title,
                    'descs': i.descs,
                    'type': i.type,
                    'money': i.money,
                    'mobile': i.mobile,
                    'trade': i.trade,
                    'status': i.status,
                    'userid': i.userid,
                    'create_time': str(i.create_time),
                    'update_time': str(i.update_time)
                })
    return jsonify({'code':200,'data':list1})



# 需求大厅无限极递归
@classidy_blue.route("/gettypesss")
def gettype():
    # list=[{"id":1,'name':'abc','son':[{"id":11,'name':'a11bc','son':[{"id":1331,'name':'a11bc'}]}]}]
    # return jsonify({"code":200,'clist':list})
    ids = request.args.get("ids")
    # 获取pid在传递过来的标签id中的id
    sql = 'select * from types where pid in ('+str(ids)+")"
    res = db.session.execute(sql)
    if res:
        idb = ''
        for i in res:
            idb = idb + str(i["id"]) + ","     # (4,),
        idslist = idb.split(",")     # ['(4', ')', '(5', ')', '']
        data = idslist[0:-1]
        strlist = ","
        idb = strlist.join(data)     # 4,5
        tlst = []
        for i in idb:
            if i != ",":
                tt = types.query.filter_by(id=i).first()
                tlst.append({
                    "id":tt.id,
                    "name":tt.name
                })
        return jsonify({"code":400,"data":tlst})
    return jsonify({"code": 200, "data": ids})

#
# 分页优化
@classidy_blue.route('/pageoptimize')
def pageoptimize():
    page = int(request.args.get('page'))
    # 每页显示多少条

    page_size = int(request.args.get('page_size'))
    # 用户量大 的是时候不能让一直访问sql数据库 需要讲数据放入redis缓存
    # 在查询的时候 把每页的数据都放入缓存里面，然后在查询的时候查找前一页数据里最大的id 然后在sql语句加上where条件
    # redis  缓存
    # pageoptimize存的第一页
    key = 'pageoptimize'+str(page)
    # 一共多少页
    pagekey = 'project_pagekey'
    # 总数
    totalkey = 'project_totalkey'
    list = r.str_get(key)
    if list:
        print('缓存')
        # 说明从缓存里读到缓存数据
        list = json.loads(list)
        tpages = int(r.str_get(pagekey).decode())
        tcount = int(r.str_get(totalkey).decode())
    else:
        # print('获取')
        # 获取总记录数
        sql = db.session.execute("select count(*) as c from (select * from project where status=3 union all select * from project1 where status=3 union all select * from project2 where status=3)t")

        tcount = ""
        for i in sql:
            tcount = i.c

        tpages = math.ceil(tcount/page_size)
        # 获取当前页的数据 在查询的时候加一个weher条件
        if page > 1:
            # 获取去前一页最大id page-1
            p = page-1
            pmes = r.str_get('pageoptimize'+str(p))
            if pmes:
                # 取最后一条
                mm = json.loads(pmes)
                print('1',mm)
                # preid = mm[:-1]['code']
                for i in  mm[:-1]:
                    preid = i['code']

                    print(preid)
                csql = db.session.execute("select * from (select * from project where status=1 union all select * from project1 where status=1 union all select * from project2 where status=1)t where code < '%s' order by code desc limit %d,%d"%(preid,0,page_size))
                list = []
                for i in csql:
                    list.append({
                        'code': str(i.code),
                        'name': i.name,
                        'title': i.title,
                        'descs': i.descs,
                        'type': i.type,
                        'money': i.money,
                        'mobile': i.mobile,
                        'trade': i.trade,
                        'status': i.status,
                        'userid': i.userid,
                        'create_time': str(i.create_time),
                        'update_time': str(i.update_time)
                    })
            else:
                start = (page-1)*page_size
                csql = db.session.execute("select * from (select * from project where status=1 union all select * from project1 where status=1 union all select * from project2 where status=1)t  order by code desc limit %d,%d"%(start,page_size))
                list = []
                for i in csql:
                    list.append({
                        'code': str(i.code),
                        'name': i.name,
                        'title': i.title,
                        'descs': i.descs,
                        'type': i.type,
                        'money': i.money,
                        'mobile': i.mobile,
                        'trade': i.trade,
                        'status': i.status,
                        'userid': i.userid,
                        'create_time': str(i.create_time),
                        'update_time': str(i.update_time)
                    })
        else:
            start = (page - 1) * page_size
            csql = db.session.execute("select * from (select * from project where status=1 union all select * from project1 where status=1 union all select * from project2 where status=1)t  order by code desc limit %d,%d" %(start, page_size))
            list = []
            for i in csql:
                list.append({
                    'code': str(i.code),
                    'name': i.name,
                    'title': i.title,
                    'descs': i.descs,
                    'type': i.type,
                    'money': i.money,
                    'mobile': i.mobile,
                    'trade': i.trade,
                    'status': i.status,
                    'userid': i.userid,
                    'create_time': str(i.create_time),
                    'update_time': str(i.update_time)
                })
                # 加入缓存
                # 转换为字符串 redis
            r.str_add(key,json.dumps(list))
            r.str_add(pagekey,tpages)
            r.str_add(totalkey,tcount)
    return jsonify({'code':200,'tpages':tpages,'data':list,'total':tcount})





